<?php
class m_users extends Zmodel{
    function __construct() {
   	    parent::__construct();
    }
    
    function authentication($users, $pwd) {
    	$this->load->database();
    	$sql = "SELECT u.user_id,
    	               u.user_login,
    	               u.user_email,
    	               u.user_pwd,
    	               u.user_group_id,
    	               u.city_id,
    	               u.date_create,
    	               gr.user_group_code,
    	               gr.user_group_desc
				FROM users u 
				LEFT JOIN user_group gr on(gr.user_group_id = u.user_group_id)
    	        WHERE u.user_login='".pg_escape_string($users)."' AND 
    	              u.user_pwd='".strtolower(sha1($pwd))."' AND
    	              u.user_group_id = 3";
        $query = $this->db->query($sql);
        $nb_rows = $query->num_rows();
        if ($nb_rows < 1) {
            return NULL;
        } else {
            return $query->row_array();
        }
    }
    
    /* user registration
     * */
    function registration($user_fname,
						  $user_lname,
						  $user_login,
						  $user_email,
						  $user_pwd,
						  $user_tel,
						  $city_id,
						  $sec_question_id,
						  $sec_answer){  	
    	$this->load->database();
    	$user_id = $this->get_next_id("users_user_id_seq");
    	
		$sql="INSERT INTO users(user_id,
		                  user_fname,
						  user_lname,
						  user_login,
						  user_email,
						  user_pwd,
						  user_tel,
						  user_group_id,
						  city_id,
						  sec_question_id,
						  date_create,
						  sec_answer)
  					VALUES(".$user_id.",
  					      '".pg_escape_string($user_fname)."',
						  '".pg_escape_string($user_lname)."',
						  '".pg_escape_string($user_login)."',
						  '".pg_escape_string($user_email)."',
						  '".pg_escape_string($user_pwd)."',
						  '".pg_escape_string($user_tel)."',
						  3,
						  '".pg_escape_string($city_id)."',
						  $sec_question_id,
						  CURRENT_TIMESTAMP,
						  '".$sec_answer."')";
		$this->db->query($sql);
		return $user_id; 				  
	 }
	 
    /* user Profiles
     * */
    function updateProfiles($user_login,
    					  $user_fname, 
						  $user_lname,
						  $user_tel,
						  $city_id,
						  $sec_question_id,
						  $sec_answer,
						  $user_photo){  	
    	$this->load->database();
		$sql="UPDATE users 
				 SET user_fname='".pg_escape_string($user_fname)."',
						  user_lname='".pg_escape_string($user_lname)."',
						  user_tel='".pg_escape_string($user_tel)."',
						  city_id='".pg_escape_string($city_id)."',
						  sec_question_id=$sec_question_id,
						  date_update=CURRENT_TIMESTAMP,
						  sec_answer='".pg_escape_string($sec_answer)."',
						  user_photo='".pg_escape_string($user_photo)."'
						  WHERE user_login='".pg_escape_string($user_login)."'";
		$this->db->query($sql);
		//return 				  
	 }
	 
	 function getPassword($users, $email){
	 	$this->load->database();
	 	$sql="SELECT user_login,
	 				 user_email 
	 			FROM users 
	 			WHERE user_login='".pg_escape_string($users)."'
	 			AND user_email='".pg_escape_string($email)."'";
	 	$query=$this->db->query($sql);
	    $nb_rows = $query->num_rows();
        if ($nb_rows < 1) {
            return NULL;
        } else {
            return $query->row_array();
        }
	 	
	 }
	 
	 function getUserVerified($users){
	 	$this->load->database();
	 	$sql="SELECT user_login
	 			FROM users
	 			WHERE user_login='".pg_escape_string($users)."'";
	 	$query=$this->db->query($sql);
	 	$nb_rows = $query->num_rows();
        if ($nb_rows < 1) {
            return NULL;
        } else {
            return $query->row_array();
        }
       /* $result=$query->result();
        $users=$result[0]->user_login;
        return $users;*/
	 }
	 
	 function getEmail($email){
	 	$this->load->database();
	 	if($email=='zuka@gozuka.com'){
	 		$sql="SELECT user_email FROM users" ;
	 	}elseif($email!='zuka@gozuka.com') {
	 		$sql="SELECT user_email 
	 			FROM users 
	 			WHERE user_email='".pg_escape_string($email)."'";
	 	}
	 	$query=$this->db->query($sql);
	 	$nb_rows = $query->num_rows();
	    if ($nb_rows < 1) {
            return NULL;
        } else {
            return $query->row_array();
        }
        
	 }
	 
	 function getEmailByUser(){
	 	$this->load->database();
		$Z_USER = Zsession::getUserFromSession();
	    $users=$Z_USER["user_id"];
	 	$sql="SELECT user_id,
	 				 user_login,
	 				 user_email 
	 			FROM users 
	 			WHERE user_id='".pg_escape_string($users)."'";
	 	$query=$this->db->query($sql);
	 	$nb_rows = $query->num_rows();
	    if ($nb_rows < 1) {
            return NULL;
        } else {
            return $query->row_array();
        }
        
	 }
	 
	 function getUserVerified1(){
	 	$this->load->database();
	 	$Z_USER = Zsession::getUserFromSession();
	    $users=$Z_USER["user_id"];
	    
	 	$sql="SELECT user_id,user_login
	 			FROM users
	 			WHERE user_id='".pg_escape_string($users)."'";
	 	$query=$this->db->query($sql);
        
 	 	$nb_rows = $query->num_rows();
	    if ($nb_rows < 1) {
            return NULL;
        } else {
            return $query->row_array();
        }
       	/*$result=$query->result();
        $users=$result[0]->user_login;
        return $users;*/
	 }
	 
	 function updatePassword($users,$pass){
	 	$this->load->database();
	 	$sql="UPDATE users 
	 		  SET user_pwd='".$pass."' 
	 		  WHERE user_login='".$users."'";
	 	$this->db->query($sql);
	 }
	 
	 function get_sec_que(){
	 	$this->load->database();
	 	$sql="SELECT sec_question_id,
	 	             sec_question_text 
	 		  FROM secret_question";
	 	return $this->db->query($sql);
	 	
	 }
	 
	 function getCityProvince() {
	 	$this->load->database();
	 	$sql="SELECT city_id,
	 	             city_name 
	 		  FROM city_province";
	 	return $this->db->query($sql);
	 }
	 
	 function getProfiles(){
	 	$this->load->database();
	 	$Z_USER = Zsession::getUserFromSession();
	    $users=$Z_USER["user_id"];
	 	$sql="SELECT  users.user_id,
					  users.user_fname,
					  users.user_lname,
					  users.user_login,
					  users.user_email,
					  users.user_pwd,
					  users.user_tel,
					  users.user_photo,
					  users.user_photo_type,
					  users.user_group_id,
					  users.date_create,
					  users.date_update,
					  (SELECT sec_question_text FROM secret_question s WHERE s.sec_question_id = users.sec_question_id LIMIT 1) as sec_question_text,users.sec_answer,
					  (SELECT city_name FROM city_province c WHERE c.city_id = users.city_id LIMIT 1) as city_name
	 			FROM users
	 			WHERE users.user_id='".pg_escape_string($users)."'";
	 	$query=$this->db->query($sql);
        
 	 	$nb_rows = $query->num_rows();
	    if ($nb_rows < 1) {
            return NULL;
        } else {
            return $query->row_array();
        }
	 }
	 
	function get_user_id($user_id){
		    $this->load->database();
            $sql="SELECT user_id,
						  user_fname,
						  user_lname,
						  user_login,
						  user_email,
						  user_pwd,
						  user_tel,
						  user_photo,
						  user_photo_type,
						  user_group_id,
						  date_create,
						  date_update,
						  sec_question_id,
						  sec_answer,
						  city_id
   			FROM users WHERE user_id=".$user_id."";
			return $query = $this->db->query($sql);
			//return $query->result_array();
		
	}
	 
}